﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OracleClient;
using NewDDSys.IDAL;
using NewDDSys.Entity;
using NewDDSys.Utility;
using System.Text;

namespace NewDDSys.ORADAL
{
    public class YhDAL:IYhDAL
    {
        public int AddYh(YhInfo yh)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, yh.Num),
                DALHelper.MakeInParam(":danwei", OracleType.VarChar, yh.Danwei),
                DALHelper.MakeInParam(":bumen", OracleType.VarChar, yh.Bumen),
                DALHelper.MakeInParam(":gang", OracleType.VarChar, yh.Gang),
                DALHelper.MakeInParam(":xm", OracleType.VarChar, yh.Xm),
                DALHelper.MakeInParam(":pass", OracleType.VarChar, yh.Pass)

            };
            return (DALHelper.RunCommand(@"insert into dd.YH (num, danwei, bumen, gang, xm, pass) values (:num, :danwei, :bumen, :gang, :xm, :pass)", prams));
        }
        public int UpdateYh(string num, string danwei, string bumen, string gang, string xm, string pass)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num),
                DALHelper.MakeInParam(":danwei", OracleType.VarChar, danwei),
                DALHelper.MakeInParam(":bumen", OracleType.VarChar, bumen),
                DALHelper.MakeInParam(":gang", OracleType.VarChar, gang),
                DALHelper.MakeInParam(":xm", OracleType.VarChar, xm),
                DALHelper.MakeInParam(":pass", OracleType.VarChar, pass)

            };
            return (DALHelper.RunCommand(@"update dd.YH set danwei = :danwei, bumen = :bumen, gang = :gang, xm = :xm, pass = :pass where num = :num", prams));
        }
        public int UpdatePass(string danwei, string bumen, string gang, string xm, string Npass)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":danwei", OracleType.VarChar, danwei),
                DALHelper.MakeInParam(":bumen", OracleType.VarChar, bumen),
                DALHelper.MakeInParam(":gang", OracleType.VarChar, gang),
                DALHelper.MakeInParam(":xm", OracleType.VarChar, xm),
                DALHelper.MakeInParam(":pass", OracleType.VarChar, Npass)

            };
            return (DALHelper.RunCommand(@"update dd.YH set pass = :pass where danwei = :danwei and bumen = :bumen and gang = :gang and xm = :xm", prams));
        }
        public int Del(string num)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)                      
            };
            return (DALHelper.RunCommand(@"delete from dd.YH where NUM = :num", prams));
        }
        public DataSet Select(string num)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)
            };
            return (DALHelper.RunProcReturn(@"select * from dd.YH where NUM = :num", prams, "Sbynum"));
        }
        public DataSet SelectAll()
        {
            return (DALHelper.RunProcReturn(@"select * from dd.YH", "SA"));
        }
        public bool IsHave(string danwei, string bumen, string gang, string xm, string pass)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":danwei", OracleType.VarChar, danwei),
                DALHelper.MakeInParam(":bumen", OracleType.VarChar, bumen),
                DALHelper.MakeInParam(":gang", OracleType.VarChar, gang),
                DALHelper.MakeInParam(":xm", OracleType.VarChar, xm),
                DALHelper.MakeInParam(":pass", OracleType.VarChar, pass)

            };
            object obj = DALHelper.ExecuteSQLScalar(@"select count(*) from dd.YH where danwei = :danwei and bumen = :bumen and gang = :gang and xm = :xm and pass = :pass", prams);
            int value = Convert.ToInt32(obj.ToString());
            if (value >= 1)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public DataSet NameByDB(string danwei, string bumen, string gang)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":danwei", OracleType.VarChar, danwei),
                DALHelper.MakeInParam(":bumen", OracleType.VarChar, bumen),
                DALHelper.MakeInParam(":gang", OracleType.VarChar, gang)

            };
            return (DALHelper.RunProcReturn(@"select distinct xm from dd.YH where danwei = :danwei and bumen = :bumen and gang = :gang", prams, "DBname"));
        }
        public string AUTOID()
        {

            string num = "";
            object time = DALHelper.ExecuteSQLScalar(@"select sysdate from dual");
            num = "yh" + time.ToString();


            return num;
        }
        public DataSet BMByDW(string danwei)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":danwei", OracleType.VarChar, danwei)

            };
            return (DALHelper.RunProcReturn(@"select distinct bumen from dd.YH where danwei = :danwei", prams, "BD"));
        }
        public DataSet GByDB(string danwei, string bumen)
        {
            OracleParameter[] prams = {
                
                DALHelper.MakeInParam(":danwei", OracleType.VarChar, danwei),
                DALHelper.MakeInParam(":bumen", OracleType.VarChar, bumen)

            };
            return (DALHelper.RunProcReturn(@"select distinct gang from dd.YH where danwei = :danwei and bumen = :bumen", prams, "DBG"));
        }
        public int UpdateBanzu(string num, string banzu)
        {
            OracleParameter[] prams = {
                DALHelper.MakeInParam(":banzu", OracleType.VarChar, banzu),
                DALHelper.MakeInParam(":num", OracleType.VarChar, num)
            };
            return (DALHelper.RunCommand(@"update dd.YH set banzu = :banzu where num = :num", prams));
        }
    }
}
